/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package controllers.courses;

import controllers.utils.DatabaseHjelper;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.enterprise.context.SessionScoped;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
import javax.inject.Named;
import javax.servlet.http.HttpServletRequest;
import models.Course;
import models.Department;
import models.Institute;
import models.StudyProgram;
/**
 *
 * @author dybvik
 */
@SessionScoped
@Named(value="myCourses")
public class MyCourses implements Serializable{
    private final List<Course> courses = new ArrayList<Course>();
    private final DatabaseHjelper dbh = DatabaseHjelper.hent();
    private Course course;
    
    public MyCourses() {
        if(courses.isEmpty()){
            updateCourses();
        }
    }

    public String navToMyCourses(){
        updateCourses();
        return "myCourses";
    }

    public String navToViewCourse(Course c){
        this.course=c;
        return "viewCourse";
    }

    public String navToViewEditCourse(Course c){
        this.course=c;
        course.setEditable(true);
        return "viewCourse";
    }

    public String navToExportCourses(){
        return "exportCourses";
    }

    private void updateCourses() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("select course.*, studyprogram.*, department.*, institutename, INSTPHONENR, INSTADDRESS, INSTNOTE"
                +" FROM (Studyprogram INNER JOIN (Course INNER JOIN studyprogramcourse ON course.COURSEID = studyprogramcourse.COURSEID)"
                +" ON Studyprogram.STUDYPROGRAMID = studyprogramcourse.STUDYPROGRAMID)"
                +" INNER JOIN Department ON studyprogram.DEPARTMENTID=department.DEPARTMENTID"
                +" INNER JOIN Institute ON department.INSTITUTEID=institute.INSTITUTEID");
            res = stmt.executeQuery();
            synchronized (courses) {
                courses.clear();
                while (res.next()){
                    Course c = new Course();
                    c.setCourseID(res.getInt("courseID"));
                    c.setCourseCode(res.getString("courseCode"));
                    c.setCourseName(res.getString("courseName"));
                    c.setCourseLevel(res.getString("courseLevel"));
                    c.setCourseCredit(res.getInt("courseCredits"));
                    c.setCredOverlapReduction(res.getInt("credOverlapReduction"));
                    c.setSemester(res.getString("semester"));
                    c.setTeachingLanguage(res.getString("teachingLanguage"));
                    c.setPrerequisites(res.getString("prerequisites"));
                    c.setCourseContent(res.getString("courseContent"));
                    c.setPedMethods(res.getString("pedMethods"));
                    c.setAssessment(res.getString("assessment"));
                    c.setGradingScale(res.getString("gradingScale"));
                    c.setUseOfExaminers(res.getString("useofexaminers"));
                    c.setReqResitExam(res.getString("reqResitExam"));
                    c.setExamAids(res.getString("examAids"));
                    c.setMandetoryWorkReq(res.getString("mandetoryWorkReq"));
                    c.setReadingList(res.getString("readingList"));
                    c.setReplacesCourse(res.getString("replacesCourse"));
                    c.setAdditionalNotes(res.getString("additionalNotes"));

                    StudyProgram sP = new StudyProgram();
                    sP.setStudyProgramID(res.getInt("studyProgramID"));
                    sP.setStudyProgramCode(res.getInt("studyProgramCode"));
                    sP.setName(res.getString("studyProgramName"));
                    sP.setApproved(res.getDate("approved"));
                    sP.setLastRevised(res.getDate("lastRevised"));
                    sP.setIsNKRFromInt(res.getInt("isNKR"));
                    sP.setApprovedBy(res.getString("approvedBy"));

                    Department dep = new Department();
                    dep.setDepartmentID(res.getInt("departmentID"));
                    dep.setName(res.getString("departmentName"));
                    dep.setAddress(res.getString("depAddress"));
                    dep.setPhoneNr(res.getString("depPhoneNr"));
                    dep.setNote(res.getString("depNote"));

                    Institute inst = new Institute();
                    inst.setInstituteID(res.getInt("instituteID"));
                    inst.setName(res.getString("instituteName"));
                    inst.setAddress(res.getString("instAddress"));
                    inst.setPhoneNr(res.getString("instPhoneNr"));
                    inst.setNote(res.getString("instNote"));

                    dep.setInstitute(inst);
                    sP.setDepartment(dep);
                    c.setStudyprogram(sP);
                    //System.out.println("Dette er i obj.: ID: " + c.getCourseID()+" Code: "+c.getCourseCode()+" Name: "+c.getCourseName());
                    courses.add(c);                         
                }
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            DatabaseHjelper.lukk(res);
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
  
        }
    }

    public void executeChanges() {
        for (Course c : courses) {
            if (c.isEditable()) {
                saveChanges(c);
                c.setEditable(false);
            }
        }
    }

    private void saveChanges(Course c) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("update course set courseCode=?, courseName=?, courseLevel=?, courseCreditS=?, credOverlapReduction=?, semester=?, teachingLanguage=?, prerequisites=?, courseContent=?, pedMethods=?, assessment=?, gradingScale=?, useOfExaminers=?, reqResitExam=?, examAids=?, mandetoryWorkReq=?, readingList=?, replacesCourse=?, additionalNotes=?, userID=? WHERE courseID=?");
            stmt.setString(1, c.getCourseCode());
            stmt.setString(2, c.getCourseName());
            stmt.setString(3, c.getCourseLevel());
            stmt.setInt(4, c.getCourseCredit());
            stmt.setInt(5, c.getCredOverlapReduction());
            stmt.setString(6, c.getSemester());
            stmt.setString(7, c.getTeachingLanguage());
            stmt.setString(8, c.getPrerequisites());
            stmt.setString(9, c.getCourseContent());
            stmt.setString(10, c.getPedMethods());
            stmt.setString(11, c.getAssessment());
            stmt.setString(12, c.getGradingScale());
            stmt.setString(13, c.getUseOfExaminers());
            stmt.setString(14, c.getReqResitExam());
            stmt.setString(15, c.getExamAids());
            stmt.setString(16, c.getMandetoryWorkReq());
            stmt.setString(17, c.getReadingList());
            stmt.setString(18, c.getReplacesCourse());
            stmt.setString(19, c.getAdditionalNotes());
            stmt.setInt(20, c.getUserID());
            stmt.setInt(21, c.getCourseID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Feil i endring av Course: " + e);
        } finally {
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public String setDeleteFlagAndNavigate(Course c) {
        c.setDeleteFlag(true);
        return "confirmDeleteCourse";
    }

    public String setDeleteFlagAndNavigate() {
        course.setDeleteFlag(true);
        return "confirmDeleteCourse";
    }

    public String executeDelete(){
        for (Course c: courses) {
            if (c.isDeleteFlag()) {
                deleteCourse(c);
                courses.remove(c);
                break;
            }
        }
        return navToMyCourses();
    }

    private void deleteCourse(Course c){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("DELETE FROM course WHERE courseID=?");
            stmt.setInt(1, c.getCourseID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Feil i sletting av Course: " + e);
        } finally {
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public String revertFlagsAndNavigate(){
        for(Course c: courses){
            if(c.isEditable()) c.setEditable(false);
            if(c.isDeleteFlag()) c.setDeleteFlag(false);
        }
        return navToMyCourses();
    }

    /*
    private String getRole(String sql){
        ExternalContext external = FacesContext.getCurrentInstance().getExternalContext();
        HttpServletRequest request = (HttpServletRequest) external.getRequest();
        if(request.isUserInRole("sysadmin")){

        }
        if(request.isUserInRole("studyadmin")){
            sql += " WHERE studyprogram.studyprogramID=" +
        }
        if(request.isUserInRole("teacher")){


        }
        if(request.isUserInRole("adminresource")){


        }
        return null;
    }
    */

    public List<Course> getCourses() {
        return courses;
    }

    public Course getCourse() {
        return course;
    }

    public void setCourse(Course course) {
        this.course = course;
    }
    
}


